package com.yellowpage.bigdata.service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.map.LinkedMap;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.yellowpage.white.common.Constants;
import com.yellowpage.white.common.PageRender;
import com.yellowpage.white.dao.QueryAssistantSQL;
import com.yellowpage.white.service.BaseServiceImpl;
import com.yellowpage.bigdata.data.O2oRetentionDay;
import com.yellowpage.bigdata.dao.O2oRetentionDayDao;

public class O2oRetentionDayServiceImpl extends BaseServiceImpl implements O2oRetentionDayService {
	  private O2oRetentionDayDao o2oRetentionDayDao;
	
	  public void setO2oRetentionDayDao(O2oRetentionDayDao o2oRetentionDayDao) {
		  this.o2oRetentionDayDao = o2oRetentionDayDao;
	  }
	
	
	  /* Smooth generate Method */ 
	  public O2oRetentionDay getO2oRetentionDay(Long id){
	      return o2oRetentionDayDao.get(id);
	  }
	  
    public List<O2oRetentionDay> getAllO2oRetentionDay(){
        return o2oRetentionDayDao.getAll();
    }
    
    public List<Map> queryO2oRetentionDayByPage(Map queryMap, PageRender pageRender) {		
        
    	String t1 = queryMap.get("query_logDate_end").equals("") ? "" : " AND A.DAY_ID <= '"+queryMap.get("query_logDate_end")+"'" ;
		String t2 = queryMap.get("logDate").equals("") ? "" : "  AND A.DAY_ID >= '"+queryMap.get("logDate")+"'" ;
    	String t3 = queryMap.get("o2oId").equals("") ? "" : "    AND A.O2O_ID =  '"+queryMap.get("o2oId")+"' ";
    	
    	System.out.println("query_logDate_end:" +t1+"logDate : "+t2+"  o2oId: " +t3);
    	
		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(
		          "SELECT  DAY_ID,"
							+"O2O_ID, "
							+"O2O_NAME, "
							+"SUM(ADDUSER) ADDUSER, "
							+"ROUND(SUM(2RETEN)*100/SUM(ADDUSER),2)  AS RETEN2, "
							+"ROUND(SUM(3RETEN)*100/SUM(ADDUSER),2)  AS RETEN3, "
							+"ROUND(SUM(7RETEN)*100/SUM(ADDUSER),2)  AS RETEN7, "
							+"ROUND(SUM(30RETEN)*100/SUM(ADDUSER),2) AS RETEN30 "
							+"FROM ( "
							+"	SELECT  "
							+"	DAY_ID, "
							+"	O2O_ID, "
							+"(SELECT X.`NAME` FROM O2O_CODE X WHERE X.ID = A.O2O_ID) AS O2O_NAME , "
							+"	CASE WHEN RETENTION_DAY='1' THEN COUNTS END AS 'ADDUSER', "
							+"	CASE WHEN RETENTION_DAY='2' THEN COUNTS END AS '2RETEN', "
							+"	CASE WHEN RETENTION_DAY='3' THEN COUNTS END AS '3RETEN', "
							+"	CASE WHEN RETENTION_DAY='7' THEN COUNTS END AS '7RETEN', "
							+"	CASE WHEN RETENTION_DAY='30' THEN COUNTS END AS '30RETEN' "
							+"	 FROM O2O_RETENTION_DAY  A WHERE   CHANNEL_NO = 'A' "  
							+ t1 + t2 + t3  
                            +" )  S  WHERE 1=1 " )  
	    			 .addCondition(" and S.DAY_ID >= :logDate ")
	    			 .addCondition(" and S.DAY_ID <= :query_logDate_end ")
	    			 .addCondition(" and S.O2O_ID = :o2oId ") ;
	    			sqlAssistant.setTail(" GROUP BY  S.DAY_ID, S.O2O_ID, S.O2O_NAME ");
		 	    	    
    

		List<Map> resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender);

		return resultList;
	}
    
    
    public List<Map> getO2oChannelRetentionDay(Map queryMap, PageRender pageRender) {
    	 
    	String t1 = queryMap.get("query_logDate_end").equals("") ? "" : " AND A.DAY_ID <= '"+queryMap.get("query_logDate_end")+"'" ;
		String t2 = queryMap.get("logDate").equals("") ? "" : "  AND A.DAY_ID >= '"+queryMap.get("logDate")+"'" ;
    	String t3 = queryMap.get("o2oId").equals("") ? "" : "    AND A.O2O_ID =  '"+queryMap.get("o2oId")+"' ";
    	String t4 = queryMap.get("channelId").equals("") ? "" : "    AND A.CHANNEL_NO =  '"+queryMap.get("channelId")+"' ";
    	
    	System.out.println("query_logDate_end:" +t1+"logDate : "+t2+"  o2oId: " +t3 + "channelId : " +  t4 );


    	QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(
	      "SELECT  DAY_ID,"
					+"O2O_ID, "
					+"O2O_NAME, "
					+"CHANNEL_NO,"
		            +"CHANNEL_NAME,"
					+"SUM(ADDUSER) ADDUSER, "
					+"ROUND(SUM(2RETEN)*100/SUM(ADDUSER),2) AS  RETEN2, "
					+"ROUND(SUM(3RETEN)*100/SUM(ADDUSER),2) AS  RETEN3, "
					+"ROUND(SUM(7RETEN)*100/SUM(ADDUSER),2) AS  RETEN7, "
					+"ROUND(SUM(30RETEN)*100/SUM(ADDUSER),2) AS RETEN30 "
					+"FROM ( "
					+"	SELECT  "
					+"	DAY_ID, "
					+"	O2O_ID, "
					+"(SELECT X.`NAME` FROM O2O_CODE X WHERE X.ID = A.O2O_ID) AS O2O_NAME , "
					+"CHANNEL_NO,  "
	                +"(SELECT  NAME FROM OMC_DIC_TABLE X  WHERE  dic_name='O2O_LOG_CHANNEL_NO' AND  X.VALUE= A.CHANNEL_NO) AS CHANNEL_NAME , "
					+"	CASE WHEN RETENTION_DAY='1' THEN COUNTS END AS 'ADDUSER', "
					+"	CASE WHEN RETENTION_DAY='2' THEN COUNTS END AS '2RETEN', "
					+"	CASE WHEN RETENTION_DAY='3' THEN COUNTS END AS '3RETEN', "
					+"	CASE WHEN RETENTION_DAY='7' THEN COUNTS END AS '7RETEN', "
					+"	CASE WHEN RETENTION_DAY='30' THEN COUNTS END AS '30RETEN' "
					+"	 FROM O2O_RETENTION_DAY  A WHERE   CHANNEL_NO <> 'A' "  
					+ t1 + t2 + t3 + t4 
	                +" )  S  WHERE 1=1 " )  
			 .addCondition(" and S.DAY_ID >= :logDate ")
			 .addCondition(" and S.DAY_ID <= :query_logDate_end ")
			 .addCondition(" and S.O2O_ID = :o2oId ") ;
			sqlAssistant.setTail(" GROUP BY  S.DAY_ID, S.O2O_ID, S.O2O_NAME , CHANNEL_NO , CHANNEL_NAME");

    	List<Map> resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender);

		return resultList;
    }
    
    
	@Override
	public Map getO2oIdMap() {
		Map dataSourceMap = new LinkedMap();
		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(" SELECT a.name as NAME, a.ID as VALUE  FROM O2O_CODE a  ");
		sqlAssistant.setTail("order by (a.ID+0)");
		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
				new HashMap());
		for (int i = 0; i < resultList.size(); i++) {
			dataSourceMap.put(resultList.get(i).get("VALUE"), resultList.get(i)
					.get("VALUE") + "-" + resultList.get(i).get("NAME"));
		}
		return dataSourceMap;
	}
    
	@Override
	public Map getO2oChannelMap() {
		Map dataSourceMap = new LinkedMap();
		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(" SELECT a.name as NAME, a.VALUE as VALUE  FROM OMC_DIC_TABLE a where dic_name='O2O_LOG_CHANNEL_NO' ");
		sqlAssistant.setTail("order by (a.VALUE+0)");
		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
				new HashMap());
		for (int i = 0; i < resultList.size(); i++) {
			dataSourceMap.put(resultList.get(i).get("VALUE"), resultList.get(i)
					.get("VALUE") + "-" + resultList.get(i).get("NAME"));
		}
		return dataSourceMap;
	}
    
 	
	
 
	  
	  
	 
	
}	